﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

/***********************************************************
* Description: SQL CLR
* Date:   2013-08-15 09:59
* Author: 刘俊
*
*    Created By: 刘俊
*  Created Date: 2013-08-15
*      Comments: 创建正则表达式匹配字符串函数。将与正则表达式匹配分部内容替换为指定字符串。
*   Modify Date: 2013-09-16   
*      Comments: 修复只替换首个匹配的bug。
************************************************************
*  __                        _____                     
* /\ \       __             /\___ \                    
* \ \ \     /\_\  __  __    \/__/\ \  __  __    ___    
*  \ \ \  __\/\ \/\ \/\ \      _\ \ \/\ \/\ \ /' _ `\  
*   \ \ \LJ \\ \ \ \ \_\ \    /\ \_\ \ \ \_\ \/\ \/\ \ 
*    \ \____/ \ \_\ \____/    \ \____/\ \____/\ \_\ \_\
*     \/___/   \/_/\/___/      \/___/  \/___/  \/_/\/_/
************************************************************/

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Regexp_Replace(string source, string regex,string replaceStr)
    {
        MatchCollection matches = Regex.Matches(source, regex);
        if (matches.Count>0)
        {
            foreach (Match match in matches)
            {
                source=source.Replace(match.Value, replaceStr);
            }
            return source;
        }
        else
        {
            return source;
        }
    }
};

